VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "clsControlNumber"
Attribute VB_GlobalNameSpace = True
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = True
'xwelx 04.2010

Option Explicit

Public Function GetAutoCtrlNo(rs As ADODB.Recordset, sRefCode As String, _
                sTblName As String, sFldName As String) As String

Dim lCtrlNoOK As Boolean
Dim sAutoNo As String
Dim oGetValue As New clsGetValue

    sAutoNo = Trim(oGetValue.GetValueFrTable("cValue", "SYSTEM_OPTION", " cCode='" & Trim(sRefCode) & "'"))

    Do Until lCtrlNoOK
        If oGetValue.IDExisting(rs, sFldName, sTblName, Left(sAutoNo, 5) + Format(CStr((CInt(Right(sAutoNo, 4)) + 1)), "00000")) Then
            sAutoNo = Left(sAutoNo, 5) + Format(CStr((CInt(Right(sAutoNo, 4)) + 1)), "00000")
        Else
            lCtrlNoOK = True
        End If
    Loop

    lCtrlNoOK = False

    If Mid(sAutoNo, 4, 2) <> Format(Date, "yy") Then
        sAutoNo = Left(sAutoNo, 3) + Format(Date, "yy") + "00000"
    Else
        sAutoNo = Left(sAutoNo, 3) + Format(Date, "yy") + Format(Right(sAutoNo, 4) + 1, "00000")
    End If

    Do Until lCtrlNoOK
        If oGetValue.IDExisting(rs, sFldName, sTblName, sAutoNo) Then
            sAutoNo = Left(sAutoNo, 5) + Format(CStr((CInt(Right(sAutoNo, 4)) + 1)), "00000")
        Else
            lCtrlNoOK = True
        End If
    Loop

    GetAutoCtrlNo = sAutoNo
    
    Set oGetValue = Nothing

End Function


Public Function ChkCtrlNo(rs As ADODB.Recordset, sRefCode As String, _
                sTblName As String, sFldName As String, sTranNo As String) As String
Dim lCtrlNoOK As Boolean
Dim strNewNo As String
Dim oGetValue As New clsGetValue
Dim oFormSetup As New clsFormSetup

    If oGetValue.IDExisting(rs, sFldName, sTblName, sTranNo) Then
        
        strNewNo = Trim(oGetValue.GetValueFrTable("cValue", "SYSTEM_OPTION", " cCode='" & Trim(sRefCode) & "'"))
        
        If Mid(strNewNo, 4, 2) <> Format(Date, "yy") Then
            strNewNo = Left(strNewNo, 3) + Format(Date, "yy") + "00000"
        Else
            strNewNo = Left(strNewNo, 3) + Format(Date, "yy") + Format(Right(strNewNo, 4) + 1, "00000")
        End If
        
        Do Until lCtrlNoOK
            If oGetValue.IDExisting(rs, sFldName, sTblName, strNewNo) Then
                strNewNo = Left(strNewNo, 5) + Format(CStr((CInt(Right(strNewNo, 4)) + 1)), "00000")
            Else
                lCtrlNoOK = True
            End If
        Loop
        
        ChkCtrlNo = strNewNo
        oFormSetup.FrmName.strNo = strNewNo
    Else
        ChkCtrlNo = sTranNo
    End If
    
    Set oGetValue = Nothing
    Set oFormSetup = Nothing
    
End Function


'Update system generated number
Public Sub UpdateControlNo(sAutoNo As String, sTranNo As String, sRefCode As String)
On Error GoTo TheSource
Dim sCtrlNo As String
Dim oGetValue As New clsGetValue
Dim oString As New clsDeclaration
Dim oDB As New clsConnection

    sCtrlNo = Trim(oGetValue.GetValueFrTable("cValue", "SYSTEM_OPTION", " cCode='" & Trim(sRefCode) & "'"))
    
    If Left(sCtrlNo, 3) + Format(Date, "yy") + "00000" = Trim(sTranNo) Then
        oString.sSQL = "UPDATE SYSTEM_OPTION SET cValue = '" & Trim(sTranNo) & "' WHERE cCode = '" & Trim(sRefCode) & "' AND cCompanyID = '" & oString.COID & "'"
        oDB.cn.Execute oString.sSQL
        Exit Sub
    End If
    
    If (Left(sTranNo, 5) & (Format(Right(sTranNo, 4), "00000"))) <> Trim(sAutoNo) Then Exit Sub
    oString.sSQL = "UPDATE SYSTEM_OPTION SET cValue = '" & Trim(sTranNo) & "' WHERE cCode = '" & Trim(sRefCode) & "' AND cCompanyID = '" & oString.COID & "'"
    oDB.cn.Execute oString.sSQL

    Set oGetValue = Nothing
TheSource:
    Exit Sub
End Sub


